<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
	<title>LIKE and RLIKE Operators | ElasticSearch 7.7 权威指南中文版</title>
	<meta name="keywords" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <meta name="description" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <!-- Give IE8 a fighting chance -->
    <!--[if lt IE 9]>
    <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
    <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
	<link rel="stylesheet" type="text/css" href="../static/styles.css" />
	<script>
	var _link = 'sql-like-rlike-operators.html';
    </script>
</head>
<body>
<div class="main-container">
    <section id="content">
        <div class="content-wrapper">
            <section id="guide" lang="zh_cn">
                <div class="container">
                    <div class="row">
                        <div class="col-xs-12 col-sm-8 col-md-8 guide-section">
                            <div style="color:gray; word-break: break-all; font-size:12px;">原英文版地址: <a href="https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-like-rlike-operators.html" rel="nofollow" target="_blank">https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-like-rlike-operators.html</a>, 原文档版权归 www.elastic.co 所有<br/>本地英文版地址: <a href="../en/sql-like-rlike-operators.html" rel="nofollow" target="_blank">../en/sql-like-rlike-operators.html</a></div>
                        <!-- start body -->
                  <div class="page_header">
<strong>重要</strong>: 此版本不会发布额外的bug修复或文档更新。最新信息请参考 <a href="https://www.elastic.co/guide/en/elasticsearch/reference/current/index.html" rel="nofollow">当前版本文档</a>。
</div>
<div id="content">
<div class="breadcrumbs">
<span class="breadcrumb-link"><a href="index.html">Elasticsearch Guide [7.7]</a></span>
»
<span class="breadcrumb-link"><a href="xpack-sql.html">SQL access</a></span>
»
<span class="breadcrumb-link"><a href="sql-functions.html">Functions and Operators</a></span>
»
<span class="breadcrumb-node">LIKE and RLIKE Operators</span>
</div>
<div class="navheader">
<span class="prev">
<a href="sql-operators-cast.html">« Cast Operators</a>
</span>
<span class="next">
<a href="sql-functions-aggs.html">Aggregate Functions »</a>
</span>
</div>
<div class="section xpack">
<div class="titlepage"><div><div>
<h2 class="title">
<a id="sql-like-rlike-operators"></a>LIKE and RLIKE Operators<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/like-rlike.asciidoc">edit</a><a class="xpack_tag" href="https://www.elastic.co/subscriptions"></a>
</h2>
</div></div></div>
<p><code class="literal">LIKE</code> and <code class="literal">RLIKE</code> operators are commonly used to filter data based on string patterns. They usually act on a field placed on the left-hand side of
the operator, but can also act on a constant (literal) expression. The right-hand side of the operator represents the pattern.
Both can be used in the <code class="literal">WHERE</code> clause of the <code class="literal">SELECT</code> statement, but <code class="literal">LIKE</code> can also be used in other places, such as defining an
<a class="xref" href="sql-index-patterns.html" title="Index patterns">index pattern</a> or across various <a class="xref" href="sql-commands.html" title="SQL Commands">SHOW commands</a>.
This section covers only the <code class="literal">SELECT ... WHERE ...</code> usage.</p>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>One significant difference between <code class="literal">LIKE</code>/<code class="literal">RLIKE</code> and the <a class="xref" href="sql-functions-search.html" title="Full-Text Search Functions">full-text search predicates</a> is that the former
act on <a class="xref" href="sql-data-types.html#sql-multi-field" title="SQL and multi-fields">exact fields</a> while the latter also work on <a class="xref" href="text.html" title="Text datatype">analyzed</a> fields. If the field used with <code class="literal">LIKE</code>/<code class="literal">RLIKE</code> doesn’t
have an exact not-normalized sub-field (of <a class="xref" href="keyword.html" title="Keyword datatype">keyword</a> type) Elasticsearch SQL will not be able to run the query. If the field is either exact
or has an exact sub-field, it will use it as is, or it will automatically use the exact sub-field even if it wasn’t explicitly specified in the statement.</p>
</div>
</div>
<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-like-operator"></a><code class="literal">LIKE</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/like-rlike.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">expression        <a id="CO72-1"></a><i class="conum" data-value="1"></i>
LIKE constant_exp <a id="CO72-2"></a><i class="conum" data-value="2"></i></pre>
</div>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO72-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>typically a field, or a constant expression</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO72-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>pattern</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Description</strong></span>: The SQL <code class="literal">LIKE</code> operator is used to compare a value to similar values using wildcard operators. There are two wildcards used in conjunction
with the <code class="literal">LIKE</code> operator:</p>
<div class="ulist itemizedlist">
<ul class="itemizedlist">
<li class="listitem">
The percent sign (%)
</li>
<li class="listitem">
The underscore (_)
</li>
</ul>
</div>
<p>The percent sign represents zero, one or multiple characters. The underscore represents a single number or character. These symbols can be
used in combinations.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT author, name FROM library WHERE name LIKE 'Dune%';

    author     |     name
---------------+---------------
Frank Herbert  |Dune
Frank Herbert  |Dune Messiah</pre>
</div>
<p>There is, also, the possibility of using an escape character if one needs to match the wildcard characters themselves. This can be done
by using the <code class="literal">ESCAPE [escape_character]</code> statement after the <code class="literal">LIKE ...</code> operator:</p>
<pre class="literallayout">SELECT name, author FROM library WHERE name LIKE 'Dune/%' ESCAPE '/';</pre>

<p>In the example above <code class="literal">/</code> is defined as an escape character which needs to be placed before the <code class="literal">%</code> or <code class="literal">_</code> characters if one needs to
match those characters in the pattern specifically. By default, there is no escape character defined.</p>
<div class="important admon">
<div class="icon"></div>
<div class="admon_content">
<p>Even though <code class="literal">LIKE</code> is a valid option when searching or filtering in Elasticsearch SQL, full-text search predicates
<code class="literal">MATCH</code> and <code class="literal">QUERY</code> are <a class="xref" href="sql-like-rlike-operators.html#sql-like-prefer-full-text" title="Prefer full-text search predicates">faster and much more powerful and are the preferred alternative</a>.</p>
</div>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-rlike-operator"></a><code class="literal">RLIKE</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/like-rlike.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">expression         <a id="CO73-1"></a><i class="conum" data-value="1"></i>
RLIKE constant_exp <a id="CO73-2"></a><i class="conum" data-value="2"></i></pre>
</div>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO73-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>typically a field, or a constant expression</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO73-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>pattern</p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Description</strong></span>: This operator is similar to <code class="literal">LIKE</code>, but the user is not limited to search for a string based on a fixed pattern with the percent sign (<code class="literal">%</code>)
and underscore (<code class="literal">_</code>); the pattern in this case is a regular expression which allows the construction of more flexible patterns.</p>
<p>For supported syntax, see <a class="xref" href="regexp-syntax.html" title="Regular expression syntax"><em>Regular expression syntax</em></a>.</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT author, name FROM library WHERE name RLIKE 'Child.* Dune';

    author     |      name
---------------+----------------
Frank Herbert  |Children of Dune</pre>
</div>
<div class="important admon">
<div class="icon"></div>
<div class="admon_content">
<p>Even though <code class="literal">RLIKE</code> is a valid option when searching or filtering in Elasticsearch SQL, full-text search predicates
<code class="literal">MATCH</code> and <code class="literal">QUERY</code> are <a class="xref" href="sql-like-rlike-operators.html#sql-like-prefer-full-text" title="Prefer full-text search predicates">faster and much more powerful and are the preferred alternative</a>.</p>
</div>
</div>
</div>

<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-like-prefer-full-text"></a>Prefer full-text search predicates<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/like-rlike.asciidoc">edit</a>
</h3>
</div></div></div>
<p>When using <code class="literal">LIKE</code>/<code class="literal">RLIKE</code>, do consider using <a class="xref" href="sql-functions-search.html" title="Full-Text Search Functions">full-text search predicates</a> which are faster, much more powerful
and offer the option of sorting by relevancy (results can be returned based on how well they matched).</p>
<p>For example:</p>
<div class="informaltable">
<table border="1" cellpadding="4px">
<colgroup>
<col class="col_1">
<col class="col_2">
</colgroup>
<tbody>
<tr>
<td align="center" valign="top"><p><span class="strong strong"><strong>LIKE/RLIKE</strong></span></p></td>
<td align="center" valign="top"><p><span class="strong strong"><strong>QUERY/MATCH</strong></span></p></td>
</tr>
<tr>
<td align="left" valign="top"><p><code class="literal"><code class="literal">foo LIKE 'bar'</code></code></p></td>
<td align="left" valign="top"><p><code class="literal"><code class="literal">MATCH(foo, 'bar')</code></code></p></td>
</tr>
<tr>
<td align="left" valign="top"><p><code class="literal"><code class="literal">foo LIKE 'bar' AND tar LIKE 'goo'</code></code></p></td>
<td align="left" valign="top"><p><code class="literal"><code class="literal">MATCH('foo^2, tar^5', 'bar goo', 'operator=and')</code></code></p></td>
</tr>
<tr>
<td align="left" valign="top"><p><code class="literal"><code class="literal">foo LIKE 'barr'</code></code></p></td>
<td align="left" valign="top"><p><code class="literal"><code class="literal">QUERY('foo: bar~')</code></code></p></td>
</tr>
<tr>
<td align="left" valign="top"><p><code class="literal"><code class="literal">foo LIKE 'bar' AND tar LIKE 'goo'</code></code></p></td>
<td align="left" valign="top"><p><code class="literal"><code class="literal">QUERY('foo: bar AND tar: goo')</code></code></p></td>
</tr>
<tr>
<td align="left" valign="top"><p><code class="literal"><code class="literal">foo RLIKE 'ba.*'</code></code></p></td>
<td align="left" valign="top"><p><code class="literal"><code class="literal">MATCH(foo, 'ba', 'fuzziness=AUTO:1,5')</code></code></p></td>
</tr>
<tr>
<td align="left" valign="top"><p><code class="literal"><code class="literal">foo RLIKE 'b.{1}r'</code></code></p></td>
<td align="left" valign="top"><p><code class="literal"><code class="literal">MATCH(foo, 'br', 'fuzziness=1')</code></code></p></td>
</tr>
</tbody>
</table>
</div>
</div>

</div>
<div class="navfooter">
<span class="prev">
<a href="sql-operators-cast.html">« Cast Operators</a>
</span>
<span class="next">
<a href="sql-functions-aggs.html">Aggregate Functions »</a>
</span>
</div>
</div>

                  <!-- end body -->
                        </div>
                        <div class="col-xs-12 col-sm-4 col-md-4" id="right_col">
                        
                        </div>
                    </div>
                </div>
            </section>
        </div>
    </section>
</div>
<script src="../static/cn.js"></script>
</body>
</html>